﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TrainingWorkshop.Model;
using System.Data.SqlClient;
using System.Data;

namespace TrainingWorkshop.NewDAL
{
	public class TeacherRepository:ITeacherRepository
	{

        private SqlConnection Conn;

        public TeacherRepository(SqlConnection TConn)
        {
            Conn = TConn;
            
        }

        public List<Teacher> SelectAll()
        {
            DataSet ds = new DataSet();
            List<Teacher> result = new List<Teacher>();
            string str = null;

            str = "select * from Teacher ";

            SqlCommand cmd = new SqlCommand(str, Conn);
            

            try
            {
                Conn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "Result");

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    Department dep = new Department() 
                    {
                       DepartmentId=Convert.ToInt32(row[3])
                    };
                    Teacher a = new Teacher 
                    {
                        TeacherId = Convert.ToInt32(row[0]),
                        TeacherName = row[1].ToString().Trim(),
                        TeacherGender = row[2].ToString().Trim(),
                        DepartMent = dep
                    };

                   

                    result.Add(a);
                }

            }
            catch (Exception e)
            {
                 e.ToString();
            
                //Response.Write("<script>alert()</script>");

            }
            finally
            {
                Conn.Close();
            }
            return result;
        }

        public List<Teacher> Select(Teacher TeacherInstance)
        {
            DataSet ds = new DataSet();
            List<Teacher> result = new List<Teacher>();
            string str = null;
         
            str = "select * from Teacher where TeacherId=@Id";

            SqlParameter par = new SqlParameter("@Id", TeacherInstance.TeacherId);
            SqlCommand cmd = new SqlCommand(str, Conn);
            cmd.Parameters.Add(par);

            try
            {
                Conn.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "Result");
                
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    Department dep = new Department()
                    {
                        DepartmentId = Convert.ToInt32(row[3])
                    };
                    Teacher a = new Teacher
                    {
                        TeacherId = Convert.ToInt32(row[0]),
                        TeacherName = row[1].ToString().Trim(),
                        TeacherGender = row[2].ToString().Trim(),
                        DepartMent = dep
                    };


                    //Teacher a = new Teacher(Convert.ToInt32(row[0]), row[1].ToString(), row[2].ToString(), Convert.ToInt32(row[3]));

                    result.Add(a);
                }

            }
            catch (Exception e)
            {
                //HttpContext.Current.Response.Write("<script>alert('Teacher Table Error')</script>");
                
            }
            finally
            {
                Conn.Close();
            }
            return result;
        }

        public void Update(Teacher TeacherInstance)
        {
            try
            {
                Conn.Open();
                string str = "update Teacher set TeacherName=@Name,TeacherGender=@Gender,DepartmentId=@DId where TeacherId=@Id";
                SqlParameter[] para = new SqlParameter[]
                {
                    new SqlParameter("@Name",TeacherInstance.TeacherName),
                    new SqlParameter("@Gender",TeacherInstance.TeacherGender),
                    new SqlParameter("@DId",TeacherInstance.DepartMent.DepartmentId),
                    new SqlParameter("@Id",TeacherInstance.TeacherId)

                };

                SqlCommand cmd = new SqlCommand(str, Conn);

                cmd.Parameters.AddRange(para);

                cmd.ExecuteNonQuery();
                
                
            }

            catch (Exception)
            {

                //HttpContext.Current.Response.Write("<script>alert('Insert Error')</script>");
                
            }

            finally
            {
                Conn.Close();
            }
        }

        public void Create(Teacher TeacherInstance) 
        {
            try
            {
                Conn.Open();
                string str = "insert into Teacher (TeacherName,TeacherGender,DepartmentId) values (@Name,@Gender,@Id)";
                SqlParameter[] para = new SqlParameter[]
                {
                    new SqlParameter("@Name",TeacherInstance.TeacherName),
                    new SqlParameter("@Gender",TeacherInstance.TeacherGender),
                    new SqlParameter("@Id",TeacherInstance.DepartMent.DepartmentId)

                };

                SqlCommand cmd = new SqlCommand(str, Conn);

                cmd.Parameters.AddRange(para);

                cmd.ExecuteNonQuery();
                
               
            }

            catch (Exception)
            {

                //HttpContext.Current.Response.Write("<script>alert('Insert Error')</script>");
                
               
            }
            finally
            {
                Conn.Close();
            }

        }

        public void Delete(Teacher TeacherInstance)
        {
            try
            {
                Conn.Open();
                string str = "Delete from Teacher where TeacherId=@Id";
                SqlParameter[] para = new SqlParameter[]
                {
                    
                    new SqlParameter("@Id",TeacherInstance.TeacherId)

                };

                SqlCommand cmd = new SqlCommand(str, Conn);

                cmd.Parameters.AddRange(para);

                cmd.ExecuteNonQuery();
                
            }

            catch (Exception)
            {

                //HttpContext.Current.Response.Write("<script>alert('Delete Error')</script>");
                
                
            }

            finally
            {
                Conn.Close();
            }
        }


    }
}
